package com.bears.god.sup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bears.god.sup.model.Dept;
import com.bears.god.sup.util.DBConnection;

public class DeptDAOImp implements IdeptDAO {
	private static final String FINDBYID = "select * from s_dept where id=?";
	private static final String FINDBY_ALL = "select * from s_dept";
	private static final String FINDBY_FIVE = "select * from s_dept LIMIT ?,?";
	private static final String INSERT = "insert into s_dept" + "(department_name,description) " + "values(?,?)";
	private static final String UPDATE = "update s_dept set " + "department_name=?,description=? " + "where id=?";

	public List<Dept> findByAll(int num1, int num2) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		List<Dept> dept = new ArrayList<Dept>();
		ResultSet rst = null;
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FINDBY_FIVE);
			prep.setInt(1, num1);
			prep.setInt(2, num2);
			rst = prep.executeQuery();
			while (rst.next()) {
				Dept d = new Dept();

				d.setId(rst.getInt("id"));
				d.setDepartmentName(rst.getString("department_name"));
				d.setDescription(rst.getString("description"));
				dept.add(d);

			}

		} finally {
			DBConnection.close(prep, conn);
		}
		return dept;
	}

	@Override
	public List<Dept> findByAll() throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		List<Dept> dept = new ArrayList<Dept>();
		ResultSet rst = null;
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FINDBY_ALL);
			rst = prep.executeQuery();
			while (rst.next()) {
				Dept d = new Dept();

				d.setId(rst.getInt("id"));
				d.setDepartmentName(rst.getString("department_name"));
				d.setDescription(rst.getString("description"));
				dept.add(d);

			}

		} finally {
			DBConnection.close(prep, conn);
		}
		return dept;
	}

	@Override
	public void save(Dept dept) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(INSERT);
			prep.setString(1, dept.departmentName);
			prep.setString(2, dept.description);

			prep.executeUpdate();
			ResultSet rst = prep.getGeneratedKeys();
			rst.next();
			int id = rst.getInt(1);
			dept.setId(id);
		} finally {
			DBConnection.close(prep, conn);

		}

	}

	@Override
	public void update(Dept dept) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(UPDATE);
			prep.setString(1, dept.getDepartmentName());
			prep.setString(2, dept.getDescription());
			prep.setInt(3, dept.getId());

			prep.executeUpdate();
		} finally {
			DBConnection.close(prep, conn);
		}
	}

	@Override
	public Dept findById(int id) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		ResultSet rst = null;
		Dept dept = null;

		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FINDBYID);
			prep.setInt(1, id);

			rst = prep.executeQuery();
			if (rst.next()) {
				dept = new Dept();

				dept.setDepartmentName(rst.getString("department_name"));
				dept.setDescription(rst.getString("description"));
				dept.setId(rst.getInt("id"));
			}
		} finally {
			DBConnection.close(prep, conn);
		}
		return dept;
	}
}
